﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using Microsoft.Win32;
using Excel = Microsoft.Office.Interop.Excel;//数据库导出Excel
using Microsoft.CSharp.RuntimeBinder;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.Data.SqlClient;
using NPOI.XSSF.UserModel;
using System.IO;

namespace EquipmentMS
{
    public partial class MainForm : Form
    {
        BaseClass.Operation oper = new BaseClass.Operation();
        public MainForm()
        {
            InitializeComponent();


        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            staDataTime.Text = "今天日期：" + DateTime.Now.ToString();
        }
        interface IPerformance
        {
            void DataBindGridViewList();
        }
        public void DataBindGridViewList()
        {
            try
            {
                DataSet P_dsDataGridView = null;
                DataSet P_dsZC = null;

                P_dsDataGridView = oper.GetDataGridViewList();
                P_dsZC = oper.GetDataSetZC();

                dgvEquipmentList.DataSource = P_dsZC.Tables[0].DefaultView;

                for (int j = 0; j < P_dsZC.Tables[0].Columns.Count; j++)
                {
                    for (int i = 1; i < P_dsDataGridView.Tables[0].Rows.Count; i++)
                        if (P_dsDataGridView.Tables[0].Rows[i]["filedName"].ToString() == P_dsZC.Tables[0].Columns[j].ColumnName)
                        {
                            dgvEquipmentList.Columns[i].HeaderText = P_dsDataGridView.Tables[0].Rows[i - 1]["title"].ToString();
                            dgvEquipmentList.Columns[i + 1].Width = Convert.ToInt16(P_dsDataGridView.Tables[0].Rows[i]["width"].ToString());
                            dgvEquipmentList.Columns[i + 1].Visible = Convert.ToBoolean(P_dsDataGridView.Tables[0].Rows[i]["visible"].ToString());
                        }
                }
                dgvEquipmentList.Columns[P_dsZC.Tables[0].Columns.Count - 1].HeaderText = P_dsDataGridView.Tables[0].Rows[P_dsDataGridView.Tables[0].Rows.Count - 1]["title"].ToString();
                dgvEquipmentList.Columns[P_dsZC.Tables[0].Columns.Count - 1].Width = Convert.ToInt16(P_dsDataGridView.Tables[0].Rows[P_dsDataGridView.Tables[0].Rows.Count - 1]["width"].ToString());
                dgvEquipmentList.Columns[P_dsZC.Tables[0].Columns.Count - 1].Visible = Convert.ToBoolean(P_dsDataGridView.Tables[0].Rows[P_dsDataGridView.Tables[0].Rows.Count - 1]["visible"].ToString());
                dgvEquipmentList.Columns[0].Visible = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
            }
        }

        private void frmMain_Load(object sender, EventArgs e)
        {


            this.DataBindGridViewList();
            this.SetTreeView();
            //搜索框的获得失去焦点
            focusAndBlur();
            //不显示出dataGridView1的最后一行空白   
            dgvEquipmentList.AllowUserToAddRows = false;
            //显示操作员
            staUnits.Text = "当前操作员：" + Admin.adminName;
        }
        private void focusAndBlur()
        {
            tbSearch.GotFocus += new EventHandler(tbSearchFocus);
            tbSearch.LostFocus += new EventHandler(tbSearchBlur);

        }
        //获取焦点
        private void tbSearchFocus(object sender, EventArgs e)
        {
            if (tbSearch.Text == "快速搜索")
            {
                tbSearch.Text = "";
            }
        }
        //失去焦点
        private void tbSearchBlur(object sender, EventArgs e)
        {
            if (tbSearch.Text == "")
            {
                tbSearch.Text = "快速搜索";
            }
        }
        private void SetTreeView()
        {

            trvFile.Nodes.Clear();
            //设置TreeView控件的菜单项
            DataSet ds = null;
            ds = oper.TreeFill();
            TreeNode RootNode = null;
            TreeNode chileNode = null;
            DataTable dt = ds.Tables[0].Copy();     //将资产列表另存一份为dt
            DataView dv = new DataView(dt);
            dv.RowFilter = "firstID = -1";
            //将数据集中的所有记录逐个根据他们之间的关系添加到树形表中去
            try
            {
                if (dv.Count > 0)
                {
                    foreach (DataRowView myRow in dv)
                    {
                        //设置根节点,然后该函数会递归添加所有子节点。
                        trvFile.Nodes.Add(RootNode = new TreeNode(myRow["zclb"].ToString()));
                        childTreeView(myRow["zclb"].ToString(), trvFile.Nodes[0], myRow);
                        trvFile.SelectedNode = trvFile.Nodes[0]; //选中第一个节点 
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
            }

            //展开节点
            trvFile.ExpandAll();
            //填充－－增加方式
            chileNode = RootNode.Nodes.Add("增加方式", "增加方式", 1);
            for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
            {
                chileNode.Nodes.Add("", ds.Tables[1].Rows[i]["zjfs"].ToString(), 2);
            }
            //填充－－使用部门
            chileNode = RootNode.Nodes.Add("使用部门", "使用部门", 1);
            for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
            {
                chileNode.Nodes.Add("", ds.Tables[2].Rows[i]["sybm"].ToString(), 2);
            }
            //填充－－使用情况
            chileNode = RootNode.Nodes.Add("使用情况", "使用情况", 1);
            for (int i = 0; i < ds.Tables[3].Rows.Count; i++)
            {
                chileNode.Nodes.Add("", ds.Tables[3].Rows[i]["syqk"].ToString(), 2);
            }
            //填充－－存放地点
            chileNode = RootNode.Nodes.Add("存放地点", "存放地点", 1);
            for (int i = 0; i < ds.Tables[4].Rows.Count; i++)
            {
                chileNode.Nodes.Add("", ds.Tables[4].Rows[i]["cfdd"].ToString(), 2);
            }
            //填充－－保管人员
            chileNode = RootNode.Nodes.Add("保管人员", "保管人员", 1);
            for (int i = 0; i < ds.Tables[5].Rows.Count; i++)
            {
                chileNode.Nodes.Add("", ds.Tables[5].Rows[i]["bgry"].ToString(), 2);
            }
        }
        private void childTreeView(string childPart, TreeNode childNode, DataRowView childRow)
        {
            string strdeptName = "";
            DataSet ds = null;
            ds = oper.TreeFill();
            DataTable dt = ds.Tables[0].Copy();
            DataView dv = new DataView(dt);
            //筛选获得当前传递过来的节点的子项，并将其添加到树形图中
            //判断方法是凡parentIndex等于传递过来的节点的absIndex的，就是该节点的子项
            dv.RowFilter = "firstID = '" + childRow["secondID"].ToString() + "'";
            //递归的添加每一个节点的所有子节点
            try
            {
                foreach (DataRowView myRow in dv)
                {
                    strdeptName = myRow["zclb"].ToString();
                    TreeNode myNode = new TreeNode(strdeptName);
                    childNode.Nodes.Add(myNode);
                    //函数递归调用，将所有节点按顺序添加完毕
                    childTreeView(strdeptName, myNode, myRow);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
            }
        }



        private void toolStripMenuItem3_Click(object sender, EventArgs e)
        {
            new EquipmentMS.BaseInfo.BaseInfoForm().ShowDialog();

        }

        private void toolStripButton4_Click(object sender, EventArgs e)
        {
            toolStripMenuItem3_Click(sender, e);
        }





        private void toolStripMenuItem11_Click(object sender, EventArgs e)
        {
            Equipment.EquipmentInsertForm equipment = new Equipment.EquipmentInsertForm();
            equipment.Owner = this;
            equipment.ShowDialog();
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            toolStripMenuItem11_Click(sender, e);
        }

        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            toolStripMenuItem12_Click(sender, e);
        }

        private void toolStripMenuItem12_Click(object sender, EventArgs e)
        {
            try
            {
                if (dgvEquipmentList.RowCount >= 1)
                {
                    Equipment.EquipmentUpdateForm frmEUpdate = new Equipment.EquipmentUpdateForm();
                    //frmEUpdate.M_frmMain = this;
                    frmEUpdate.Owner = this;
                    frmEUpdate.M_str_BH = dgvEquipmentList.SelectedCells[1].Value.ToString();
                    frmEUpdate.ShowDialog();

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
            }
        }

        private void toolStripButton10_Click(object sender, EventArgs e)
        {
            this.DataBindGridViewList();
        }

        private void toolStripMenuItem13_Click(object sender, EventArgs e)
        {
            if (dgvEquipmentList.RowCount > 1)
            {
                if (MessageBox.Show("是否删除当前资料!", "系统提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    int i = oper.DeleteZcMain(dgvEquipmentList.SelectedCells[1].Value.ToString());
                    this.DataBindGridViewList();
                }
            }
        }


        private void toolStripMenuItem23_Click(object sender, EventArgs e)
        {
            new EquipmentMS.SystemSet.UserForm().ShowDialog();
        }

        private void toolStripMenuItem15_Click(object sender, EventArgs e)
        {
            DataSet P_dsDataGridView = null;
            DataSet P_dsZC = null;

            P_dsDataGridView = oper.GetDataGridViewList();
            P_dsZC = oper.GetDataSetZC();
            try
            {
                for (int j = 0; j < P_dsZC.Tables[0].Columns.Count; j++)
                {
                    for (int i = 1; i < P_dsDataGridView.Tables[0].Rows.Count; i++)
                    {
                        if (P_dsDataGridView.Tables[0].Rows[i]["filedName"].ToString() == P_dsZC.Tables[0].Columns[j].ColumnName)
                        {
                            int d = oper.DataGridViewSetWidth(dgvEquipmentList.Columns[j].Index, dgvEquipmentList.Columns[j].Width);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
            }
            this.DataBindGridViewList();
        }



        private void toolStripMenuItem24_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("calc.exe");
        }

        private void toolStripMenuItem25_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("notepad.exe");
        }





        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {
            this.SetTreeView();
        }

        private void trvFile_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
        {

            DataSet ds = null;
            if (e.Node.Text == "全部资产")
            {
                ds = oper.GetDataSetZC();
            }
            else
            {

                switch (e.Node.Parent.Text.Trim())
                {
                    case "资产类别":
                        ds = oper.GetDataSetBaseZcMain_zclb(e.Node.Text);
                        break;
                    case "增加方式":
                        ds = oper.GetDataSetBaseZcMain_zjfs(e.Node.Text);
                        break;
                    case "使用部门":
                        ds = oper.GetDataSetBaseZcMain_sybm(e.Node.Text);
                        break;
                    case "使用情况":

                        ds = oper.GetDataSetBaseZcMain_syqk(e.Node.Text);
                        break;
                    case "存放地点":
                        ds = oper.GetDataSetBaseZcMain_cfdd(e.Node.Text);
                        break;
                    case "保管人员":
                        ds = oper.GetDataSetBaseZcMain_bgry(e.Node.Text);
                        break;
                    default:
                        return;
                }
            }
            dgvEquipmentList.DataSource = ds.Tables[0].DefaultView;
        }





        private void toolStripMenuItem26_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("确定要退出登录？", "注销提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start("EquipmentMS");
                //彻底关闭系统//关闭进程
                System.Environment.Exit(0);

            }

        }



        private void toolStripMenuItem27_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("确认退出？", "退出提示：", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
            {

                //彻底关闭系统
                System.Environment.Exit(0);
            }
        }

        private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
        {

        }

        private void dgvEquipmentList_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            toolStripMenuItem12_Click(sender, e);
        }

        private void dgvEquipmentList_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F8 || e.KeyCode == Keys.Delete)
            {
                toolStripMenuItem13_Click(sender, e);
            }
        }



        private void MainForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (MessageBox.Show("确认退出？", "退出提示：", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
            {
                //彻底关闭系统
                System.Environment.Exit(0);
            }
            else
            {
                e.Cancel = true;
            }

        }

        private void 最大化ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Maximized;
        }

        private void 最小化ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
        }

        private void 注销ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            toolStripMenuItem26_Click(sender, e);
        }

        private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("请确认退出！", "系统提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
            {
                Application.Exit();
            }

        }

        private void staUnits_Click(object sender, EventArgs e)
        {



        }

        private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            toolStripMenuItem12_Click(sender, e);
        }

        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dgvEquipmentList.RowCount >= 1)
            {
                if (MessageBox.Show("是否删除当前资料!", "系统提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    int i = oper.DeleteZcMain(dgvEquipmentList.SelectedCells[1].Value.ToString());
                    this.DataBindGridViewList();
                }
            }
        }
        private void dgvEquipmentList_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void trvFile_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }

        private void toolStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {

        }

        private void toolStripMenuItem9_Click(object sender, EventArgs e)
        {

        }

        private void toolStripButton3_Click(object sender, EventArgs e)
        {

        }

        private void pictureBox1_Click(object sender, EventArgs e)
        {
            if (tbSearch.Text == "快速搜索")
            {

            }
            else
            {

                try
                {
                    DataSet P_dsDataGridView = null;
                    DataSet P_dsZC = null;

                    P_dsDataGridView = oper.GetDataGridViewList();
                    P_dsZC = oper.searchGetDataSetZC(tbSearch.Text);

                    dgvEquipmentList.DataSource = P_dsZC.Tables[0].DefaultView;

                    for (int j = 0; j < P_dsZC.Tables[0].Columns.Count; j++)
                    {
                        for (int i = 1; i < P_dsDataGridView.Tables[0].Rows.Count; i++)
                            if (P_dsDataGridView.Tables[0].Rows[i]["filedName"].ToString() == P_dsZC.Tables[0].Columns[j].ColumnName)
                            {
                                dgvEquipmentList.Columns[i].HeaderText = P_dsDataGridView.Tables[0].Rows[i - 1]["title"].ToString();
                                dgvEquipmentList.Columns[i + 1].Width = Convert.ToInt16(P_dsDataGridView.Tables[0].Rows[i]["width"].ToString());
                                dgvEquipmentList.Columns[i + 1].Visible = Convert.ToBoolean(P_dsDataGridView.Tables[0].Rows[i]["visible"].ToString());
                            }
                    }
                    dgvEquipmentList.Columns[P_dsZC.Tables[0].Columns.Count - 1].HeaderText = P_dsDataGridView.Tables[0].Rows[P_dsDataGridView.Tables[0].Rows.Count - 1]["title"].ToString();
                    dgvEquipmentList.Columns[P_dsZC.Tables[0].Columns.Count - 1].Width = Convert.ToInt16(P_dsDataGridView.Tables[0].Rows[P_dsDataGridView.Tables[0].Rows.Count - 1]["width"].ToString());
                    dgvEquipmentList.Columns[P_dsZC.Tables[0].Columns.Count - 1].Visible = Convert.ToBoolean(P_dsDataGridView.Tables[0].Rows[P_dsDataGridView.Tables[0].Rows.Count - 1]["visible"].ToString());
                    dgvEquipmentList.Columns[0].Visible = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
                }
            }

        }

        private void tbSearch_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)//如果输入的是回车键
            {
                this.pictureBox1_Click(sender, e);//触发button事件
            }
        }

        private void pictureBox1_MouseEnter(object sender, EventArgs e)
        {
            pictureBox1.Size = new Size(32, 32);
        }

        private void pictureBox1_MouseLeave(object sender, EventArgs e)
        {
            pictureBox1.Size = new Size(30, 30);
        }

        private void reToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataBindGridViewList();
        }

        private void modifyToolStripMenuItem_Click(object sender, EventArgs e)
        {
            toolStripMenuItem3_Click(sender, e);
        }

        private void toolStripButton3_Click_1(object sender, EventArgs e)
        {
            try
            {
                if (dgvEquipmentList.RowCount >= 1)
                {
                    //  EquipmentMS.Equipment.EquipmentUpdateForm frmEUpdate = new EquipmentMS.Equipment.EquipmentUpdateForm();
                    //  frmEUpdate.M_frmMain = this;
                    //   frmEUpdate.M_str_BH = dgvEquipmentList.SelectedCells[1].Value.ToString();
                    //  frmEUpdate.ShowDialog();
                    //   frmEUpdate.M_str_BH = dgvEquipmentList.SelectedCells[1].Value.ToString();
                    string bh = dgvEquipmentList.SelectedCells[1].Value.ToString();
                    string sql = string.Format("update  tb_zcMain set syqk='使用中' where bh='{0}'", bh);
                    DBHelper.ExecuteNonQuery(sql); DataBindGridViewList();
                    string path = null;//@"D:/Equipment/QRcode/";//二维码保存的路径
                    EquipmentMS.BaseClass.cZcMain zcMain = new EquipmentMS.BaseClass.cZcMain();
                    if (!Directory.Exists(@"D:\Equipment\QRcode\"))//判断D盘下是否存在此文件夹
                    {
                        //如果不存在就创建file文件夹
                        Directory.CreateDirectory(@"D:\Equipment\QRcode\");
                        path = @"D:\Equipment\QRcode\";//二维码保存的路径
                        if (!Directory.Exists(@"D:\Equipment\QRcode\"))//为避免用户不存在此分盘，再次判断是否存在此文件夹
                        {
                            //如果不存在就创建file文件夹
                            Directory.CreateDirectory(@"C:\Program Files\Equipment\QRcode\");
                            path = @"C:\Program Files\Equipment\QRcode\";//二维码保存的路径
                            if (!Directory.Exists(@"C:\Program Files\Equipment\QRcode\"))//若在不存在此盘，用户自选择存储位置
                            {
                                OpenFileDialog openFileDialog = new OpenFileDialog();
                                openFileDialog.Filter = "图片格式(.jpeg)|*.jpeg";
                                openFileDialog.FileName = zcMain.BH;
                                openFileDialog.ShowDialog();
                                path = openFileDialog.FileName;
                            }
                        }
                    }
                    string filename = bh + ".jpg";
                    lendSuccess lendSuccess = new lendSuccess(path + filename);
                    lendSuccess.ShowDialog();

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
            }
        }
        //把datagridviewer导出为Excel

        private void toolStripButton5_Click(object sender, EventArgs e)
        {
            try
            {
                if (dgvEquipmentList.RowCount >= 1)
                {

                    string bh = dgvEquipmentList.SelectedCells[1].Value.ToString();
                    string sql = string.Format("update  tb_zcMain set syqk='未使用' where syqk='使用中' and bh='{0}'", bh);
                    DBHelper.ExecuteNonQuery(sql);
                    DataBindGridViewList();
                    MessageBox.Show("归还成功！");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
            }
        }

        private void toolStripButton6_Click(object sender, EventArgs e)
        {

            returnAssets returnAssets = new returnAssets();
            returnAssets.Owner = this;
            returnAssets.ShowDialog();
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            DataTable dt = CreateTable();

            if (DataTableToExcel(dt))
            {
                MessageBox.Show("导出成功！");
            }

        }
        //创建DataTable 方法 
        public DataTable CreateTable()
        {
            DataTable dataTable = new DataTable("ExportData");//*新建一张表
            try
            {
                //查询数据
                DataSet P_dsDataGridView = null;
                DataSet P_dsZC = null;

                P_dsDataGridView = oper.GetDataGridViewList();
                P_dsZC = oper.GetDataSetZC();

                //设置Excel表头
                string[] FiveHeader = new string[P_dsDataGridView.Tables[0].Rows.Count];
                for (int i = 0; i < P_dsDataGridView.Tables[0].Rows.Count; i++)
                {
                    string tit = P_dsDataGridView.Tables[0].Rows[i]["title"].ToString();
                    FiveHeader[i] = tit;
                }
                foreach (string TemStr in FiveHeader)
                {
                    DataColumn strNameColumn = new DataColumn();
                    strNameColumn.DataType = typeof(String);
                    strNameColumn.ColumnName = TemStr;
                    dataTable.Columns.Add(strNameColumn);       //*建立五列数据
                }

                ////设置表的数据
                for (int j = 0; j < P_dsZC.Tables[0].Rows.Count; j++)
                {
                    DataRow row = P_dsZC.Tables[0].Rows[j];//数据库里面的行
                    DataRow rowData = dataTable.NewRow();   //*Excel建立行数据
                    for (int i = 0; i < P_dsDataGridView.Tables[0].Rows.Count; i++)//循环列
                    {
                        string filedName = P_dsDataGridView.Tables[0].Rows[i]["filedName"].ToString();
                        string tit = P_dsDataGridView.Tables[0].Rows[i]["title"].ToString();
                        object v = row[filedName];
                        rowData[tit] = v;

                    }
                    dataTable.Rows.Add(rowData);

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载列表时出现异常，请联系管理员！\n原因：" + ex.Message);
            }
            return dataTable;
        }

        //3.导出DataTable即ExportExcel
        public bool DataTableToExcel(DataTable dataTable)
        {
            string strName = null;
            bool result = false;
            IWorkbook workbook = null;
            System.IO.FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            try
            {
                if (dataTable != null && dataTable.Rows.Count > 0)
                {
                    workbook = new HSSFWorkbook();
                    sheet = workbook.CreateSheet("Sheet1");
                    int rowCount = dataTable.Rows.Count;
                    int columnCount = dataTable.Columns.Count;

                    //设置列头  
                    row = sheet.CreateRow(0);

                    for (int i = 0; i < columnCount; i++)
                    {
                        cell = row.CreateCell(i);
                        cell.SetCellValue(dataTable.Columns[i].ColumnName);
                    }


                    for (int i = 0; i < rowCount; i++)
                    {
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            cell = row.CreateCell(j);
                            cell.SetCellValue(dataTable.Rows[i][j].ToString());
                        }
                    }

                    //保存路径
                    SaveFileDialog of = new SaveFileDialog();//声明保存对话框
                    of.Filter = "Exce文件(*.xls)|*.xls";//设置过滤器
                    of.FileName = "固定资产管理报表";//设置默认文件名字
                    DialogResult dr= of.ShowDialog();//打开保存对话框
                    if (DialogResult.Cancel  ==dr )
                    {
                        return result;
                    }
                    strName = of.FileName;

                    using (fs = System.IO.File.OpenWrite(@strName))
                    {
                        workbook.Write(fs);
                        result = true;

                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return result;
            }
        }

        private void ImportToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string strName = null;
            OpenFileDialog of = new OpenFileDialog();
            of.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dr = of.ShowDialog();

            if (DialogResult.OK == dr)
            {



                strName = of.FileName;

                try
                {


                    FileStream fs = new FileStream(strName, FileMode.Open, FileAccess.Read);
                    HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
                                                            //for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是xls文件中总共的表数
                                                            //{
                                                            // MessageBox.Show(wk.NumberOfSheets + "");

                    ISheet sheet = wk.GetSheetAt(0); //读取当前表数据     
                    for (int j = 1; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
                    {
                        IRow row = sheet.GetRow(j); //读取当前行数据
                        if (row != null)
                        {
                            // MessageBox.Show(row.Cells[0].StringCellValue);
                            //MessageBox.Show(row.Cells[0].StringCellValue);
                            //this.textBox2.AppendText(row.Cells[4].StringCellValue);
                            //sb.Append("{name:\"王威\",img:\"\"}");
                            EquipmentMS.BaseClass.cZcMain zcMain = new EquipmentMS.BaseClass.cZcMain();

                            zcMain.BH = row.Cells[0].StringCellValue;
                            zcMain.MC = row.Cells[1].StringCellValue;
                            zcMain.XH = row.Cells[2].StringCellValue;
                            zcMain.Zclb = row.Cells[3].StringCellValue;
                            zcMain.Xxpz = row.Cells[4].StringCellValue;
                            zcMain.Gjbh = row.Cells[5].StringCellValue;
                            zcMain.Sccj = row.Cells[6].StringCellValue;
                            zcMain.Ccrq = DateTime.Parse(row.Cells[7].StringCellValue);
                            zcMain.Zjfs = row.Cells[8].StringCellValue;
                            zcMain.Sybm = row.Cells[9].StringCellValue;
                            zcMain.Syqk = row.Cells[10].StringCellValue;
                            zcMain.Cfdd = row.Cells[11].StringCellValue;
                            zcMain.Bgry = row.Cells[12].StringCellValue;
                            zcMain.Rzrq = DateTime.Parse(row.Cells[13].StringCellValue);
                            zcMain.SL = int.Parse(row.Cells[14].StringCellValue);
                            zcMain.DW = row.Cells[15].StringCellValue;
                            zcMain.DJ = float.Parse(row.Cells[16].StringCellValue);
                            zcMain.YZ = float.Parse(row.Cells[17].StringCellValue);
                            zcMain.Ljzj = float.Parse(row.Cells[18].StringCellValue);
                            zcMain.JZ = float.Parse(row.Cells[19].StringCellValue);
                            zcMain.Jczl = float.Parse(row.Cells[20].StringCellValue);
                            zcMain.Zjff = row.Cells[21].StringCellValue;
                            zcMain.Nx = int.Parse(row.Cells[22].StringCellValue);
                            zcMain.Tm = row.Cells[25].StringCellValue;
                            zcMain.Xlh = row.Cells[26].StringCellValue;
                            zcMain.Brand = row.Cells[27].StringCellValue;
                            int i = oper.InsertZcMain(zcMain);

                        }

                    }
                    MessageBox.Show("导入成功");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导入错误:" + ex.Message);
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {

        }
    }
}
